﻿-- http://forum.xbmc.org/showthread.php?tid=96097
SELECT 1 AS DBObjectType, F.idFile AS ObjectID,
CONVERT(UNHEX(HEX(CONVERT(CONVERT(CONCAT(P.strPath, F.strFilename) USING utf8) USING latin1))), CHAR) AS Filename
FROM files AS F
INNER JOIN path AS P ON P.idPath = F.idPath

UNION ALL

SELECT 2 AS DBObjectType, idActor AS ObjectID, 
REPLACE(REPLACE(strThumb, '<thumb>', ''), '</thumb>', '') AS Filename
FROM actors
WHERE strThumb LIKE '%smb:%'

UNION ALL

SELECT 2 AS DBObjectType, idActor AS ObjectID, 
CONCAT('actor', LOWER(strActor)) AS Filename
FROM actors
WHERE strThumb LIKE '%smb:%'

UNION ALL

-- TV Show banners/fanart use the literal path to the show, Eg. "smb://server/TV Shows/Lost/"
SELECT DISTINCT 3 AS DBObjectType, P.idPath,
P.strPath AS Filename
FROM tvshowlinkpath TVSLP
INNER JOIN path P ON P.idPath = TVSLP.idPath

UNION ALL

SELECT DISTINCT 3 AS DBObjectType, TS.idShow AS ObjectID, 
CONCAT(CONCAT(CONCAT('season', P.strPath), 'season '), E.c12) AS Filename
FROM tvshow TS
INNER JOIN tvshowlinkepisode TSLE ON TSLE.idShow = TS.idShow
INNER JOIN episode E ON E.idEpisode = TSLE.idEpisode
INNER JOIN tvshowlinkpath TSLP ON TSLP.idShow = TS.idShow
INNER JOIN path P ON P.idPath = TSLP.idPath

UNION ALL

SELECT DISTINCT 3 AS DBObjectType, TS.idShow AS ObjectID, 
CONCAT(CONCAT('season', P.strPath), '* all seasons') AS Filename
FROM tvshow TS
INNER JOIN tvshowlinkepisode TSLE ON TSLE.idShow = TS.idShow
INNER JOIN episode E ON E.idEpisode = TSLE.idEpisode
INNER JOIN tvshowlinkpath TSLP ON TSLP.idShow = TS.idShow
INNER JOIN path P ON P.idPath = TSLP.idPath
            
UNION ALL

SELECT DISTINCT 3 AS DBObjectType, TS.idShow AS ObjectID, 
CONCAT(CONCAT('season', P.strPath), 'season 0') AS Filename
FROM tvshow TS
INNER JOIN tvshowlinkepisode TSLE ON TSLE.idShow = TS.idShow
INNER JOIN episode E ON E.idEpisode = TSLE.idEpisode
INNER JOIN tvshowlinkpath TSLP ON TSLP.idShow = TS.idShow
INNER JOIN path P ON P.idPath = TSLP.idPath
            
UNION ALL

SELECT DISTINCT 3 AS DBObjectType, TS.idShow AS ObjectID, 
CONCAT(CONCAT('season', P.strPath), 'specials') AS Filename
FROM tvshow TS
INNER JOIN tvshowlinkepisode TSLE ON TSLE.idShow = TS.idShow
INNER JOIN episode E ON E.idEpisode = TSLE.idEpisode
INNER JOIN tvshowlinkpath TSLP ON TSLP.idShow = TS.idShow
INNER JOIN path P ON P.idPath = TSLP.idPath

UNION ALL

SELECT DISTINCT 4 AS DBObjectType, idSet AS ObjectID,
CONCAT(CONCAT('videodb://1/7/', CAST(idSet AS CHAR(10))), '/') AS Filename
FROM sets
